package ibms_rcp.dao;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.Room;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class LeaseManager {
	
	/**
	 * 办理业主租赁
	 * @param companyId
	 * @param room
	 */
	public static void addLease(long companyId, Room room){
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//更改房间的租赁信息
			EstateManager.updateRoomLeaseInfo(room);
			//在m2mnap_moi_rs中对业主与房间进行关系
			addMoiRS(companyId, room.getId());
			//在m2mnap_estate_lease_log中添加记录
			addLeaseLog(companyId, room);
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	/**
	 * 办理房间退租
	 * 1)更改房间租赁状态
	 * 2)删除m2mnap_moi_rs中业主与房间对应关系
	 * 3)在m2mnap_estate_lease_log中修改记录的实际退租时间
	 * @param companyId
	 * @param roomId
	 */
	public static void endLease(long companyId, long roomId){
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//更改房间的租赁状态
			EstateManager.clearRoomLeaseInfo(roomId);
			//在m2mnap_moi_rs中删除业主与房间关系
			delMoiRS(companyId, roomId);
			//在m2mnap_estate_lease_log中修改实际退租时间
			updateEndLeaseLog(companyId, roomId);
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	/**
	 * 添加租赁记录
	 * @param companyId
	 * @param room
	 */
	public static void addLeaseLog(long companyId, Room room) {
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			// 插入company的moi
			String leaseLogSql = "insert into m2mnap_estate_lease_log (id, user_id, a_moi_id, z_moi_id, lease_time, start_time, expected_end_time) "
					+ "values(nextval('m2mnap_estate_lease_log_seq'), ?, ?, ?, now(), ?, ?) returning id;";
			PreparedStatement pstmt = conn.prepareStatement(leaseLogSql);
			pstmt.setLong(1, 3);//设置user_id
			pstmt.setLong(2, companyId);
			pstmt.setLong(3, room.getId());
			pstmt.setDate(4, java.sql.Date.valueOf(room.getBeginLeaseDate()));
			pstmt.setDate(5, java.sql.Date.valueOf(room.getEndLeaseDate()));
			// 返回插入的lease log的id
			long leaseLogId = 0;
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				leaseLogId = rs.getLong(1);
			rs.close();
			pstmt.close();
			System.out.println("插入租赁记录(id, 操作人, 公司id, 房间id, 添加时间, 始租日期, 预计到租日期) values ("
							+ leaseLogId + ", "
							+ 3 + ", "
							+ companyId + ", "
							+ room.getId() + ", "
							+ new Date() + ", "
							+ room.getBeginLeaseDate() + ", "
							+ room.getEndLeaseDate() + ");");
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("LeaseManager addLeaseLog() called.");
	}
	
	/**
	 * 添加业主与房间的关系
	 * @param companyId
	 * @param roomId
	 */
	public static void addMoiRS(long companyId, long roomId){
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			String mocAgSql = "select id from m2mnap_moc_ag where moc_id=? and code=?;";
			PreparedStatement pstmt = conn.prepareStatement(mocAgSql);
			//查询业主的moc_ag id
			long company_ag_id = 0;
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			pstmt.setString(2, "1-Basic");
			ResultSet rs = pstmt.executeQuery();
			if(rs.next())
				company_ag_id = rs.getLong(1);
			rs.close();
			//查询房间的moc_ag id
			long room_ag_id = 0;
			pstmt.setLong(1, Constants.MOC_ROOM_ID);
			pstmt.setString(2, "1-Hire");
			rs = pstmt.executeQuery();
			if(rs.next())
				room_ag_id = rs.getLong(1);
			rs.close();
			pstmt.close();
			// 插入company的moi
			String moiRSSql = "insert into m2mnap_moi_rs (id, a_moi_id, a_moc_id, z_moi_id, z_moc_id, old_relationship_id, new_relationship_id, last_changed_time) "
					+ "values(nextval('m2mnap_moi_rs_seq'), ?, ?, ?, ?, ?, ?, now());";
			//添加业主->房间关系
			pstmt = conn.prepareStatement(moiRSSql);
			pstmt.setLong(1, companyId);
			pstmt.setLong(2, Constants.MOC_COMPANY_ID);
			pstmt.setLong(3, roomId);
			pstmt.setLong(4, Constants.MOC_ROOM_ID);
			pstmt.setLong(5, company_ag_id);
			pstmt.setLong(6, company_ag_id);
			pstmt.executeUpdate();
			//添加房间->业主关系
			pstmt = conn.prepareStatement(moiRSSql);
			pstmt.setLong(1, roomId);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			pstmt.setLong(3, companyId);
			pstmt.setLong(4, Constants.MOC_COMPANY_ID);
			pstmt.setLong(5, room_ag_id);
			pstmt.setLong(6, room_ag_id);
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("LeaseManager addMoiRS() called.");
	}
	
	/**
	 * 删除业主与房间的关系
	 * @param companyId
	 * @param roomId
	 */
	public static void delMoiRS(long companyId, long roomId){
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			String moiRSSql = "delete from m2mnap_moi_rs where a_moi_id=? and z_moi_id=?;";
			//删除业主->房间关系
			PreparedStatement pstmt = conn.prepareStatement(moiRSSql);
			pstmt.setLong(1, companyId);
			pstmt.setLong(2, roomId);
			pstmt.executeUpdate();
			//删除房间->业主关系
			pstmt = conn.prepareStatement(moiRSSql);
			pstmt.setLong(1, roomId);
			pstmt.setLong(2, companyId);
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
			System.out.println("删除业主与房间关系：业主="+companyId+", 房间="+roomId);
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("LeaseManager addMoiRS() called.");
	}
	
	/**
	 * 修改实际退租时间
	 * @param companyId
	 * @param roomId
	 */
	public static void updateEndLeaseLog(long companyId, long roomId) {
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			// 插入company的moi
			String leaseLogSql = "update m2mnap_estate_lease_log set reality_end_time=now() where a_moi_id=? and z_moi_id=?;";
			PreparedStatement pstmt = conn.prepareStatement(leaseLogSql);
			pstmt.setLong(1, companyId);
			pstmt.setLong(2, roomId);
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("LeaseManager addLeaseLog() called.");
	}
	
	/**
	 * 修改租赁预收费功能
	 * @param companyId
	 * @param room
	 */
	public static void modifyLease(Room room){
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//更改房间的租赁信息
			//TODO 修改预收费信息
			EstateManager.updateRoomChargeInfo(room);
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args){
		long companyId = 4769;
		Room room = new Room();
		room.setId(4818);
		room.setBeginLeaseDate("2011-09-28");
		room.setEndLeaseDate("2012-09-28");
		room.setBeginChargeDate("2011-12-01");
		room.setPreCharge(true);
//		addLeaseLog(companyId, room);
//		addMoiRS(4769, 4818);
//		addLease(companyId, room);
	}
}
